package edu.ncsu.csc.itrust.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import edu.ncsu.csc.itrust.DBUtil;
import edu.ncsu.csc.itrust.beans.ProcedureBean;
import edu.ncsu.csc.itrust.beans.loaders.ProcedureBeanLoader;
import edu.ncsu.csc.itrust.dao.DAOFactory;
import edu.ncsu.csc.itrust.exception.DBException;

public class OfficeVisitProcedureDAO {
	
	/** Obligatory parent object */
	private DAOFactory factory;	

	/** Obligatory object */
	private ProcedureBeanLoader procedureBeanLoader = new ProcedureBeanLoader(true);
	
	/**
	 * The typical constructor.
	 * @param factory The {@link DAOFactory} associated with this DAO, which is used for obtaining SQL connections, etc.
	 */
	public OfficeVisitProcedureDAO(DAOFactory factory) {
		this.factory = factory;
	}
	
	/**
	 * Returns all of the procedures associated with the given office visit
	 * 
	 * @param visitID The unique ID of the office visit.
	 * @return A java.util.List of procedures.
	 * @throws DBException
	 */
	public List<ProcedureBean> getProcedures(long visitID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		List<ProcedureBean> procs;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("select * from ovprocedure ovp, officevisits ov, cptcodes cpt where ov.id=? and ovp.visitid=? and cpt.code=ovp.cptcode");
			ps.setLong(1, visitID);
			ps.setLong(2, visitID);
			ResultSet rs = ps.executeQuery();
			procs = procedureBeanLoader.loadList(rs);
		} 
		catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} 
		finally {
			DBUtil.closeConnection(conn, ps);
		}
		
		return procs;
	}
	
	/**
	 * Edits an existing procedure bean.
	 * 
	 * @param proc The newly updated procedure bean.
	 * @return A long indicating the ID of the newly updated proc bean.
	 * @throws DBException
	 */
	public long editProcedure(ProcedureBean proc) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			String statement = "UPDATE OVProcedure " +
				"SET CPTCode=? WHERE ID=?";
			ps = conn.prepareStatement(statement);
			ps.setString(1, proc.getCPTCode());
			ps.setLong(2, proc.getOvProcedureID());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}	

	/**
	 * Removes the given procedure from its office visit
	 * 
	 * @param ovProcedureID The unique ID of the office visit procedure.
	 * @throws DBException
	 */
	public void removeProcedureFromOfficeVisit(long ovProcedureID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("DELETE FROM OVProcedure WHERE ID=? ");
			ps.setLong(1, ovProcedureID);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Adds the given CPT codes to the given office visit
	 * 
	 * @param cptCode
	 * @param visitID
	 * @return
	 * @throws DBException
	 */
	public long addProcedureToOfficeVisit (String cptCode, long visitID, String hcpid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("INSERT INTO OVProcedure (CPTCode,VisitID,HCPID) VALUES (?,?,?)");
			ps.setString(1, cptCode);
			ps.setLong(2, visitID);
			ps.setString(3, hcpid);
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}	
}
